IF OBJECT_ID('dbo.fnGetMultiCriteriaCode') IS NOT NULL
BEGIN
    DROP FUNCTION dbo.fnGetMultiCriteriaCode
    IF OBJECT_ID('dbo.fnGetMultiCriteriaCode') IS NOT NULL
        PRINT '<<< FAILED DROPPING FUNCTION dbo.fnGetMultiCriteriaCode >>>'
    ELSE
        PRINT '<<< DROPPED FUNCTION dbo.fnGetMultiCriteriaCode >>>'
END
GO
-- =======================================================================
-- Function Name: dbo.fnGetMultiCriteriaCode()
--
-- Author:      Kevin Cashman, Parallax Consulting LLC
--
-- Create date: 8/1/2007
--
-- Description: Parses long string to table result of shorter strings
--             
--
-- Parameters:  @array varchar(2000)
--              
-- Returns:     table (Code VARCHAR(100) PRIMARY KEY)
--
-- SampleCall:
-- SELECT @PersonName = fnGetMultiCriteriaCode ( 1234 )
--
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 7/?/07      KC        Created
-- 8/17/08     KC        Changed Naming to match file name, changed result
--                            to VARCHAR(100)
-- =======================================================================
CREATE  FUNCTION dbo.fnGetMultiCriteriaCode (@array varchar(2000))
RETURNS  @Criteria table 
        (Code VARCHAR(100) PRIMARY KEY)
AS
BEGIN

     DECLARE @separator char(1)
     SET @separator = ','

     DECLARE @separatorposition int 
     DECLARE @arrayvalue varchar(1000) 
     
     SET @array = @array + ','
     
     WHILE patindex('%,%' , @array) <> 0 
     BEGIN
     
       SELECT @separatorposition =  patindex('%,%' , @array)
       SELECT @arrayvalue = left(@array, @separatorposition - 1)
     
          
          INSERT @Criteria
          VALUES (@arrayvalue)
                

       SELECT @array = stuff(@array, 1, @separatorposition, '')
     END

     RETURN
END
go
SET ANSI_NULLS OFF
go
SET QUOTED_IDENTIFIER OFF
go
IF OBJECT_ID('dbo.fnGetMultiCriteriaCode') IS NOT NULL
    PRINT '<<< CREATED FUNCTION dbo.fnGetMultiCriteriaCode >>>'
ELSE
    PRINT '<<< FAILED CREATING FUNCTION dbo.fnGetMultiCriteriaCode >>>'
go
GRANT SELECT ON dbo.fnGetMultiCriteriaCode TO InsightReport,Insight
go

